/**   
* @Title: WarehouseService.java 
* @Package com.csis.system.service 
* @Description: TODO
* @author dailey  
* @date 2012-10-7
* @version V1.0   
*/
package com.csis.system.service;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.csis.framework.db.BaseDao;
import com.csis.framework.domain.Condition;
import com.csis.framework.domain.FormBean;
import com.csis.framework.util.ServiceLocator;
import com.csis.system.common.IStaticData;
import com.csis.system.common.convert.Converter.ConvertArray;
import com.csis.system.domain.User;
import com.csis.system.domain.WareUserMap;
import com.csis.system.domain.Warehouse;
import com.csis.system.domain.bean.WarehouseBean;
import com.csis.system.domain.bean.WarehouseBeanFormBean;

/**
 * @author dailey
 *
 */
@Service("warehouseService")
public class WarehouseService {

	private Log log = LogFactory.getLog(getClass());

	@Autowired
	private BaseDao baseDao;

	public Warehouse load(Integer id) {
		return baseDao.load(Warehouse.class, id);
	}

	public List<Warehouse> getAllWarehouse() {
		return baseDao.query4SingleTable(Warehouse.class, "SELECT * FROM " + IStaticData.TABLE_WAREHOUSE);
	}

	public FormBean<Warehouse> list(FormBean<Warehouse> formBean) {
		String sql = "SELECT * FROM " + IStaticData.TABLE_WAREHOUSE + " WHERE 1=1 ";
		Condition condition = formBean.getCondition();
		if (condition != null) {
			sql = condition.getCondition(sql);
		}
		// importance
		formBean.getPage().setMaxCount(getMaxCount());
		sql = formBean.getPage().perPageSql(sql);
		List<Warehouse> beans = baseDao.query4SingleTable(Warehouse.class, sql);
		formBean.setBeans(beans);
		return formBean;
	}

	public WarehouseBeanFormBean listWareLeft(WarehouseBeanFormBean formBean) throws Exception {

		String sql = "";
		sql = preWareLeftSql(formBean);
		Condition condition = formBean.getCondition();
		if (condition != null) {
			sql = condition.getCondition(sql);
		}
		// importance
		formBean.getPage().setMaxCount(getWareLeftMaxCount());
		sql = formBean.getPage().perPageSql(sql);

		String[] columns = new String[] { "ware_id", "product_id", "product_name", "product_count" };
		Type[] types = new Type[] { Hibernate.INTEGER, Hibernate.INTEGER, Hibernate.STRING, Hibernate.INTEGER };
		List<WarehouseBean> warehouseBeans = baseDao.query(WarehouseBean.class, sql, null, columns, types);
		formBean.setBeans(warehouseBeans);
		return formBean;
	}

	/**
	 * @return
	 */
	public int getWareLeftMaxCount() {
		String sql = "SELECT * FROM (" + preWareLeftSql(null) + ") tt";
		return baseDao.getMaxCount(sql);
	}

	/**
	 * @return
	 */
	public String preWareLeftSql(WarehouseBeanFormBean formBean) {
		String sql;
		StringBuffer buff = new StringBuffer();
		buff.append(" SELECT ");
		buff.append("   ware_id, eep.product_id, product_name, product_count ");
		buff.append(" FROM ");
		buff.append("  	(SELECT ");
		buff.append("  	 ware_id, product_id, SUM(product_count) AS product_count ");
		buff.append("  	 FROM ");
		buff.append(" 	 	((SELECT ");
		buff.append("         	 ware_id, product_id, SUM(product_count) AS product_count ");
		buff.append("    	 FROM ");
		buff.append("         	entrylist ");
		buff.append(" 	 	 GROUP BY ware_id , product_id) UNION (SELECT  ");
		buff.append("	      	ware_id, product_id, - SUM(product_count) AS product_count ");
		buff.append("    	 FROM ");
		buff.append("		 	 picklist ");
		buff.append("	  	GROUP BY ware_id , product_id)) ep ");
		buff.append("	 WHERE 1=1 ");
		if (formBean != null && formBean.getWareId() != null) {
			buff.append("	 AND ware_id = " + formBean.getWareId());
		}
		buff.append(" 	GROUP BY ware_id , product_id) eep, ");
		buff.append(" 	product ");
		buff.append(" WHERE ");
		buff.append(" eep.product_id = product.product_id ");
		if (formBean != null && formBean.getMonitorCount() != null) {
			buff.append("	 AND product_count <= " + formBean.getMonitorCount() + " ");
		}
		sql = buff.toString();
		return sql;
	}

	/**
	 * @return
	 */
	public int getMaxCount() {
		String sql = "SELECT * FROM " + IStaticData.TABLE_WAREHOUSE;
		return baseDao.getMaxCount(sql);
	}

	@Transactional
	public void save(Warehouse warehouse) {
		log.info("WareService>>SaveWare>>" + warehouse);
		if (warehouse.getId() == null) {
			baseDao.persist(warehouse);
		} else {
			baseDao.merge(warehouse);
		}
	}

	@Transactional
	public void remove(Integer id) {
		baseDao.remove(Warehouse.class, id);
	}

	@Transactional
	public void remove(ConvertArray<Integer> ids) {
		if (ids == null)
			return;
		for (Integer id : ids) {
			remove(id);
		}
	}

	@Transactional
	public void assignUser(Warehouse warehouse, ConvertArray<Integer> ids) {
		if (ids == null)
			return;
		WareUserMapService wareUserMapService = ServiceLocator.getInstance().getService(WareUserMapService.class);
		UserService userService = ServiceLocator.getInstance().getService(UserService.class);
		wareUserMapService.remove(warehouse);
		for (Integer id : ids) {
			User user = userService.load(id);
			if (user == null)
				continue;
			WareUserMap wareUserMap = new WareUserMap(id, warehouse.getId());
			wareUserMapService.save(wareUserMap);
		}

	}

	/**
	 * @param name
	 * @return
	 */
	public List<Warehouse> getWaresByName(String name) {
		return baseDao.query4SingleTable(Warehouse.class, "SELECT * FROM " + IStaticData.TABLE_WAREHOUSE + " WHERE "
				+ IStaticData.COLUMN_WAREHOUSE_WARENAME + " = ?", name);
	}

}
